--1
SELECT * FROM AU_CLIENTS WHERE SUBSTR(ClientNoTel,2,3) IN ('514','450');

SELECT * FROM AU_CLIENTS;

INSERT INTO AU_CLIENTS VALUES (3,'PIERRE',1,'F','AC','FR','JACQUES','PIERRE','M','ADRESSE','VILLE','G1B 4M4', 'CANADA','(514) 444-4444','','EMAIL','','1');
INSERT INTO AU_CLIENTS VALUES (4,'ERER',1,'F','AC','FR','JACQUES','PIERRE','M','ADRESSE','VILLE','G1B 4M4', 'CANADA','(450) 444-4444','','EMAIL','','1');

--3 pas sur
SELECT VENTEID, SUM(QTEACHETEE*PRIXUNITAIREOPTION) AS "CoutTotalOptions" FROM AU_VENTESOPTIONS GROUP BY VENTEID;
SELECT * FROM AU_VENTESOPTIONS;
select * from au_ventes;
select * from au_ventesoptions;

INSERT INTO AU_VENTESOPTIONS (VENTEID, OPTIONCD,MODECD,PRIXUNITAIREOPTION,QTEACHETEE)
VALUES (2,1,'ESCROTEE', 15.25,20);

--5
SELECT distinct ClientID FROM AU_Ventes WHERE TotalTaxes > (SELECT AVG(TotalTaxes) FROM AU_Ventes);

INSERT INTO AU_VENTES (VENTEID, NOSERIEVEHICULE,CLIENTID, MODEFINCD, EMPLID, TAUXESCOMPTESAISONNIER,DATEVENTE,PRIXSUGGERE,
PRIXVENTEVEHICULE,ESCOMPTESAISONNIER,INDICATEURFIDELITE, TAUXESCOMPTEFIDELITE, COUTTOTALOPTION)
values(SEQ_VENTEID.NEXTVAL,'MAZD1201567',2,'VISA',2,0.32,SYSDATE,512.23,0.12,100.25,1,1.25,150.12);
SELECT AVG(TotalTaxes) FROM AU_Ventes;
select totaltaxes from au_ventes;
select * from au_clients;
--7
SELECT AU_OptionsDisponibles.ModeCd, AU_Options.OptionCdDescFr FROM AU_OptionsDisponibles
INNER JOIN AU_Options ON AU_OptionsDisponibles.OptionCd = AU_Options.OptionCd;

--9
SELECT VenteID FROM AU_Ventes WHERE (EscompteSaisonnier > 0 OR IndicateurFidelite = '1') AND CoutTotalOption = 0;

--11
SELECT ClientID,ClientPrenom,ClientNom FROM AU_Clients WHERE ClientNom LIKE 'T%' AND ClientID IN 
(SELECT ClientID FROM AU_Ventes WHERE VenteID IN (SELECT VenteID FROM AU_VentesOptions GROUP BY VenteID HAVING COUNT(VenteID) = 2));

--4
SELECT VenteID, ClientPrenom,(SELECT SUM(QteAchetee) FROM AU_VentesOptions WHERE AU_Ventes.VenteId = AU_VentesOptions.VenteId GROUP BY VenteID) AS "NombreOptions" 
FROM AU_Ventes
INNER JOIN AU_Clients ON AU_Ventes.ClientID = AU_Clients.ClientID
WHERE VenteID IN (SELECT VenteID FROM AU_VentesOptions WHERE PrixUnitaireOption >= 500 GROUP BY VenteID HAVING SUM(QteAchetee) >2);

SELECT COUNT(VenteID) FROM AU_VentesOptions WHERE VenteId = AU_VentesOptions.VenteId GROUP BY VenteID;

SELECT VenteID FROM AU_VentesOptions GROUP BY VenteID HAVING COUNT(VenteID) >2;

SELECT * from AU_VentesOptions;